package com.zxl.working.mapper.backend;

import com.zxl.working.entity.backend.InvocerAlert;
import com.zxl.working.entity.backend.SellerBill;
import com.zxl.working.entity.backend.SellerInfo;
import com.zxl.working.entity.backend.SellerTax;
import com.zxl.working.entity.backend.ServiceInfo;
import com.zxl.working.entity.dto.ServiceInvoice;
import org.apache.ibatis.annotations.*;
import org.springframework.beans.factory.annotation.Qualifier;

import java.util.List;

@Mapper
@Qualifier("backEndSqlSessionTemplate")
public interface ServiceMapper {

    @Select("select * from service_info where login_name = #{loginName} and service_state = 'Y'")
    @Results({
            @Result(property = "serviceId",column = "service_id"),
            @Result(property = "serviceName",column = "service_name"),
            @Result(property = "serviceCom",column = "service_com"),
            @Result(property = "serviceCode",column = "service_code"),
            @Result(property = "serviceTax",column = "service_tax"),
            @Result(property = "serviceOrg",column = "service_org"),
            @Result(property = "serviceLegal",column = "service_legal"),
            @Result(property = "serviceTaxCode",column = "service_tax_code"),
            @Result(property = "serviceAddress",column = "service_address"),
            @Result(property = "servicePhone",column = "service_phone"),
            @Result(property = "serviceBank",column = "service_bank"),
            @Result(property = "serviceAccount",column = "service_account"),
            @Result(property = "serviceState",column = "service_state"),
            @Result(property = "serviceBankName",column = "service_bank_name"),
            @Result(property = "loginId",column = "login_id"),
            @Result(property = "loginName",column = "login_name")
    })
    ServiceInfo qryServiceInfo(@Param("loginId") String loginId,@Param("loginName") String loginName);

    @Select("select a.* from service_info a,service_seller_relation b where a.service_id = b.service_id and b.ref_state = 'Y' and a.service_state = 'Y' and b.seller_id = #{sellerId}")
    @Results({
            @Result(property = "serviceId",column = "service_id"),
            @Result(property = "serviceName",column = "service_name"),
            @Result(property = "serviceCom",column = "service_com"),
            @Result(property = "serviceCode",column = "service_code"),
            @Result(property = "serviceTax",column = "service_tax"),
            @Result(property = "serviceOrg",column = "service_org"),
            @Result(property = "serviceLegal",column = "service_legal"),
            @Result(property = "serviceTaxCode",column = "service_tax_code"),
            @Result(property = "serviceAddress",column = "service_address"),
            @Result(property = "servicePhone",column = "service_phone"),
            @Result(property = "serviceBank",column = "service_bank"),
            @Result(property = "serviceAccount",column = "service_account"),
            @Result(property = "serviceState",column = "service_state"),
            @Result(property = "serviceBankName",column = "service_bank_name"),
            @Result(property = "loginId",column = "login_id"),
            @Result(property = "loginName",column = "login_name")
    })
    List<ServiceInfo> qryAllServiceInfo(@Param("sellerId") String sellerId);

    @Select("<script>select a.*,b.seller_bank,b.seller_bank_account,b.business_license_code,b.seller_mobile,b.seller_address,b.seller_name,b.seller_company_name," +
            "c.service_code,c.service_bank,c.service_bank_name,c.service_account,c.service_com,c.service_address,c.service_phone,c.service_name,d.recive_person_name as rname,d.mobile,d.address,b.tax_payer_code " +
            "from seller_bill a " +
            "left join seller_info b on a.seller_id = b.seller_id " +
            "LEFT JOIN service_info c on a.service_id = c.service_id " +
            "left join seller_consignee d on a.recive_person_name = d.recive_person_id " +
            "where a.service_id = #{serviceId} " +
            "<when test='status!=-1'>" +
            "and a.status = #{status} " +
            "</when>" +
            "<when test='sellerName!=null '>" +
            "and b.seller_company_name like concat('%',#{sellerName},'%') " +
            "</when>" +
            "order by create_time desc limit #{start},#{pageSize}</script>")
    @Results({
            @Result(property = "billCode",column = "bill_code"),
            @Result(property = "recivePersonName",column = "recive_person_name"),
            @Result(property = "billTypeId",column = "bill_type_id"),
            @Result(property = "expressCode",column = "express_code"),
            @Result(property = "status",column = "status"),
            @Result(property = "taxableTotal",column = "taxable_total"),
            @Result(property = "orderId",column = "order_id"),
            @Result(property = "orderSource",column = "order_source"),
            @Result(property = "createTime",column = "create_time"),
            @Result(property = "sellerCompanyName",column = "seller_company_name"),
            @Result(property = "billContent",column = "bill_content"),
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "serviceId",column = "service_id"),
            @Result(property = "sellerName",column = "seller_name"),
            @Result(property = "sellerBank",column = "seller_bank"),
            @Result(property = "sellerBankAccount",column = "seller_bank_account"),
            @Result(property = "businessLicenseCode",column = "business_license_code"),
            @Result(property = "sellerMobile",column = "seller_mobile"),
            @Result(property = "sellerAddress",column = "seller_address"),
            @Result(property = "enterpriseCode",column = "service_code"),
            @Result(property = "bankName",column = "service_bank"),
            @Result(property = "bankAccountName",column = "service_bank_name"),
            @Result(property = "bankCode",column = "service_account"),
            @Result(property = "serviceComName",column = "service_com"),
            @Result(property = "serviceAddress",column = "service_address"),
            @Result(property = "servicePhone",column = "service_phone"),
            @Result(property = "serviceName",column = "service_name"),
            @Result(property = "remark",column = "remark"),
            @Result(property = "expressComName",column = "express_com_name"),
            @Result(property = "billNumber",column = "bill_number"),
            @Result(property = "picUrl",column = "pic_url"),
            @Result(property = "rname",column = "rname"),
            @Result(property = "mobile",column = "mobile"),
            @Result(property = "address",column = "address"),
            @Result(property = "taxPayerCode",column = "tax_payer_code"),
    })
    List<ServiceInvoice> qryServiceInvoice(@Param("serviceId") String serviceId,@Param("status") Integer status,@Param("sellerName") String sellerName,@Param("start") Integer start,@Param("pageSize") Integer pageSize);
    
    @Select("<script>select a.*,b.seller_bank,b.seller_bank_account,b.business_license_code,b.seller_mobile,b.seller_address,b.seller_name,b.seller_company_name," +
            "c.service_code,c.service_bank,c.service_bank_name,c.service_account,c.service_com,c.service_address,c.service_phone,c.service_name,d.recive_person_name as rname,d.mobile,d.address,b.tax_payer_code " +
            "from seller_bill a " +
            "left join seller_info b on a.seller_id = b.seller_id " +
            "LEFT JOIN service_info c on a.service_id = c.service_id " +
            "left join seller_consignee d on a.recive_person_name = d.recive_person_id " +
            "where a.service_id = #{serviceId} " +
            "<when test='status!=-1'>" +
            "and a.status = #{status} " +
            "</when>" +
            "order by create_time desc</script>")
    @Results({
            @Result(property = "billCode",column = "bill_code"),
            @Result(property = "recivePersonName",column = "recive_person_name"),
            @Result(property = "billTypeId",column = "bill_type_id"),
            @Result(property = "expressCode",column = "express_code"),
            @Result(property = "status",column = "status"),
            @Result(property = "taxableTotal",column = "taxable_total"),
            @Result(property = "orderId",column = "order_id"),
            @Result(property = "orderSource",column = "order_source"),
            @Result(property = "createTime",column = "create_time"),
            @Result(property = "sellerCompanyName",column = "seller_company_name"),
            @Result(property = "billContent",column = "bill_content"),
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "serviceId",column = "service_id"),
            @Result(property = "sellerName",column = "seller_name"),
            @Result(property = "sellerBank",column = "seller_bank"),
            @Result(property = "sellerBankAccount",column = "seller_bank_account"),
            @Result(property = "businessLicenseCode",column = "business_license_code"),
            @Result(property = "sellerMobile",column = "seller_mobile"),
            @Result(property = "sellerAddress",column = "seller_address"),
            @Result(property = "enterpriseCode",column = "service_code"),
            @Result(property = "bankName",column = "service_bank"),
            @Result(property = "bankAccountName",column = "service_bank_name"),
            @Result(property = "bankCode",column = "service_account"),
            @Result(property = "serviceComName",column = "service_com"),
            @Result(property = "serviceAddress",column = "service_address"),
            @Result(property = "servicePhone",column = "service_phone"),
            @Result(property = "serviceName",column = "service_name"),
            @Result(property = "remark",column = "remark"),
            @Result(property = "expressComName",column = "express_com_name"),
            @Result(property = "billNumber",column = "bill_number"),
            @Result(property = "picUrl",column = "pic_url"),
            @Result(property = "rname",column = "rname"),
            @Result(property = "mobile",column = "mobile"),
            @Result(property = "address",column = "address"),
            @Result(property = "taxPayerCode",column = "tax_payer_code"),
    })
    List<ServiceInvoice> export(@Param("serviceId") String serviceId,@Param("status") Integer status);

    @Select("<script>" +
            "select count(1) as cnt " +
            "from seller_bill a " +
            "left join seller_info b on a.seller_id = b.seller_id " +
            "LEFT JOIN service_info c on a.service_id = c.service_id " +
            "left join seller_consignee d on a.recive_person_name = d.recive_person_id " +
            "where a.service_id = #{serviceId} " +
            "<when test='status!=-1'>" +
            "and a.status = #{status} " +
            "</when>" +
            "</script>")
    Integer qryServiceInvoiceCnt(@Param("serviceId") String serviceId,@Param("status")Integer status);

    @Update("update seller_bill set express_code=#{expressCode},express_com_name=#{expressComName},bill_number=#{billNumber},remark=#{remark},status=#{status} where bill_code = #{billCode}")
    void updBill(SellerBill sellerBill);
    
    @Select("select SUM(taxable_total) from seller_bill where service_id = #{serviceId} and `status` ='1' and date_format(create_time,'%Y-%m')=date_format(now(),'%Y-%m')")
    Integer findByMoney(@Param("serviceId")String serviceId);
    
    @Select("select count(*) from seller_bill where service_id = #{serviceId} and `status` ='1' and date_format(create_time,'%Y-%m')=date_format(now(),'%Y-%m')")
    Integer totalSum(@Param("serviceId")String serviceId);
    
    @Update("update seller_bill set express_code=#{expressCode},bill_number=#{billNumber} where bill_code = #{billCode}")
    void updexpress(SellerBill sellerBill);

    @Update("update seller_bill set status = 2 where bill_code = #{billCode}")
    void billFail(@Param("billCode") String billCode);

    @Select("select a.*,b.seller_name,b.seller_company_name,c.service_name " +
            "from seller_tax a,seller_info b,service_info c " +
            "where a.seller_id = b.seller_id and a.service_id = c.service_id and a.service_id = #{serviceId} " +
            "limit #{start},#{pageSize}")
    @Results({
            @Result(property = "id",column = "id"),
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "serviceId",column = "service_id"),
            @Result(property = "taxPic",column = "tax_pic"),
            @Result(property = "taxMonth",column = "tax_month"),
            @Result(property = "taxUrl",column = "tax_url"),
            @Result(property = "createTime",column = "create_time"),
            @Result(property = "updateTime",column = "update_time"),
            @Result(property = "sellerName",column = "seller_name"),
            @Result(property = "serviceName",column = "service_name"),
            @Result(property = "sellerCompanyName",column = "seller_company_name"),
    })
    List<SellerTax> qryTaxsByServiceId(@Param("serviceId") String serviceId,@Param("start") Integer start,@Param("pageSize") Integer pageSize);

    @Select("select count(1) as cnt " +
            "from seller_tax a,seller_info b,service_info c " +
            "where a.seller_id = b.seller_id and a.service_id = c.service_id and a.service_id = #{serviceId} ")
    Integer qryTaxsByServiceIdCnt(@Param("serviceId") String serviceId);

    @Insert("insert into seller_tax(id,seller_id,service_id,tax_pic,tax_month,tax_url) values(#{id},#{sellerId},#{serviceId},#{taxPic},#{taxMonth},#{taxUrl})")
    void insTax(SellerTax sellerTax);

    @Select("select a.* from seller_info a,service_seller_relation b where a.seller_id = b.seller_id and b.ref_state = 'Y' and b.service_id = #{serviceId}")
    @Results({
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "importId",column = "import_id"),
            @Result(property = "sellerName",column = "seller_name"),
            @Result(property = "sellerCompanyName",column = "seller_company_name"),
            @Result(property = "sellerMobile",column = "seller_mobile"),
            @Result(property = "sellerCity",column = "seller_city"),
            @Result(property = "loadDate",column = "load_date"),
            @Result(property = "maxPay",column = "max_pay"),
            @Result(property = "minPay",column = "min_pay"),
            @Result(property = "taxPayerCode",column = "tax_payer_code"),
            @Result(property = "sellerAddress",column = "seller_address"),
            @Result(property = "sellerPhone",column = "seller_phone"),
            @Result(property = "sellerBank",column = "seller_bank"),
            @Result(property = "sellerBankAccount",column = "seller_bank_account"),
            @Result(property = "sellerState",column = "seller_state"),
            @Result(property = "loginId",column = "login_id"),
            @Result(property = "loginName",column = "login_name"),
            @Result(property = "creditCode",column = "credit_code"),
            @Result(property = "businessLicenseFile",column = "business_license_file"),
            @Result(property = "isAuthentication",column = "is_authentication"),
            @Result(property = "businessLicenseCode",column = "business_license_code"),
    })
    List<SellerInfo> qrySellerInfoByService(@Param("serviceId") String serviceId);


    @Select("select bill_code,taxable_total from seller_bill where bill_code like #{orderId} order by bill_code desc limit 1")
    String qryLastOrderId(@Param("orderId")String orderId);
    
    @Select("select * from invocer_alert where service_id = #{serviceId}")
    @Results({
        @Result(property = "id",column = "id"),
        @Result(property = "serviceId",column = "service_id"),
        @Result(property = "invoiceMoney",column = "invoice_money"),
        @Result(property = "datadicValue",column = "datadic_value"),
        @Result(property = "invoiceSum",column = "invoice_sum"),
})
    InvocerAlert findByInvice(String serviceId);

    @Insert("insert into invocer_alert(service_id,datadic_value,invoice_money,invoice_sum) values(#{serviceId},#{invoiceMail},#{invoiceMoney},#{invoiceSum})")
	void insertInvoice(@Param("serviceId")String serviceId,@Param("invoiceMoney")Integer invoiceMoney, @Param("invoiceMail")String invoiceMail,@Param("invoiceSum")Integer invoiceSum);

    @Update("update invocer_alert set datadic_value=#{invoiceMail},invoice_money=#{invoiceMoney},invoice_sum=#{invoiceSum} where service_id = #{serviceId}")
	void updateInvoice(@Param("serviceId")String serviceId,@Param("invoiceMoney") Integer invoiceMoney,@Param("invoiceMail") String invoiceMail,@Param("invoiceSum")Integer invoiceSum);
}
